const Koa = require("koa");
const KoaRouter = require("koa-router");
const KoaBodyParser = require("koa-bodyparser");
const mysql = require("mysql");

// 创建一个 web 应用
const app = new Koa();
// 创建一个 web 路由
const router = new KoaRouter();

// 定义业务接口
// 添加
router.post("/invoices", async (ctx) => {
    // 解析参数
    const {
        type,
        name,
        sn = "",
        email = "",
        is_zz = 0,
        is_mr = 0,
    } = ctx.request.body;
    // 校验发票类型 is not a number
    if (!(!isNaN(type) && [1, 2, 3].includes(Number(type)))) {
        ctx.body = {
            code: 400,
            message: "发票类型不正确",
        };
        return;
    }
    // 校验邮箱
    if (!/^\w+@\w+\.com$/.test(email)) {
        ctx.body = {
            code: 400,
            message: "邮箱格式不正确",
        };
        return;
    }
    if (!(!isNaN(is_zz) && [0, 1].includes(Number(is_zz)))) {
        ctx.body = {
            code: 400,
            message: "专用类型不正确",
        };
        return;
    }
    if (!(!isNaN(is_mr) && [0, 1].includes(Number(is_mr)))) {
        ctx.body = {
            code: 400,
            message: "默认类型不正确",
        };
        return;
    }
    // 当前这个发票需要设置成默认的发票
    // 所以，我们要把数据库中，已经是默认的发票给它修改成非默认的
    if (Number(is_mr) === 1) {
        await send("update invoice set is_mr = 0 where is_mr = 1");
    }

    // 连接 MYSQL 服务器，发送 SQL 语句
    const sql = `insert into invoice (type, name, sn, email, is_zz, is_mr) values (?, ?, ?, ?, ?, ?)`;
    const params = [type, name, sn, email, is_zz, is_mr];
    const res = await send(sql, params);
    if (res.affectedRows === 1) {
        ctx.body = {
            code: 201,
            message: "添加成功",
        };
    }
});

// 查询
router.get("/invoices", async (ctx) => {
    // 接收查询参数
    let { pagenum, pagesize, keyword } = ctx.query;

    // 如果有搜索参数
    if (keyword) {
        let sql = "select * from invoice where name like ? or sn like ?";
        let params = [`%${keyword}%`, `%${keyword}%`];
        let res = await send(sql, params);
        ctx.body = {
            code: 200,
            data: res,
            message: "请求成功",
        };
        return;
    }

    // 如果没有传入查询参数，就返回所有的
    if (isNaN(pagenum) || isNaN(pagesize)) {
        const res = await send("select * from invoice");
        // HTTP 响应码规范
        ctx.body = {
            code: 200,
            data: res,
            message: "请求成功",
        };
        return;
    }
    // 如果有分页参数
    pagenum = Number(pagenum);
    pagesize = Number(pagesize);
    // 计算 limit 起始位置
    const startIndex = (pagenum - 1) * pagesize;
    // 计算总条数
    let sql = "select count(*) as total from invoice";
    let res = await send(sql);
    const total = res[0]["total"];
    // 查询分页内容
    sql = "select * from invoice order by is_mr desc limit ?, ?";
    let params = [startIndex, pagesize];
    const list = await send(sql, params);
    // 返回结果
    ctx.body = {
        code: 200,
        data: {
            list,
            total,
        },
        message: "获取列表成功",
    };
});

// 修改
router.put("/invoices/:id", async (ctx) => {
    // ...
});

// 删除
router.delete("/invoices/:id", async (ctx) => {
    // 解构参数
    const { id } = ctx.params;
    // 删除
    const res = await send("delete from invoice where id = ?", [id]);
    // 响应删除成功
    if (res.affectedRows === 1) {
        ctx.body = {
            code: 204,
            message: "删除成功",
        };
    }
});

// 加载中间件
app.use(KoaBodyParser());
app.use(router.routes());

// 监听端口，提供服务
app.listen(5000, () => {
    console.log("启动了");
});

function send(sql, params = []) {
    // 创建连接配置
    const con = mysql.createConnection({
        host: "localhost",
        port: 3306,
        user: "root",
        password: "root",
        database: "invoice",
    });

    // 为什么要返回 Promise？
    // 因为在 async 函数内，想配合 await 来等待异步处理结果返回后，再根据结果决定响应给客户端的内容
    return new Promise((resolve, reject) => {
        // 发送 SQL
        con.query(sql, params, (error, data) => {
            if (error) {
                reject(error);
            } else {
                resolve(data);
            }
        });

        // 关闭数据库
        con.end();
    });
}
